//201902104030_何琛选
package cn.edu.sdjzu.xg.bysj.dao;

import cn.edu.sdjzu.xg.bysj.domain.School;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.List;
import java.util.TreeSet;


public final class SchoolDao {
    private static SchoolDao schoolDao = new SchoolDao();
    private static Collection<School> schools;

    // 不允许创建本类对象
    private SchoolDao() {
    }

    public static SchoolDao getInstance() {
        return schoolDao;
    }

    public int count(Connection connection) throws SQLException{
        return CommonDao.getInstance().count(connection,"School");
    }

    /**
     * 返回某页上的School对象
     * @param pagination 分页对象（页号，每页面对象数）
     * @return School对象集合
     * @throws SQLException
     */
    public Collection<School> findAll(
            Connection connection, List<Condition> conditionList_json_str, Pagination pagination)
            throws SQLException {
        //创建集合类对象，用来保存所有记录代表的School对象
        //TreeSet按compare（Object o）方法指定的顺序排序
        Collection<School> desiredSchools = new TreeSet<School>();
        //获取数据库连接
        int totalNum = SchoolDao.getInstance().count(connection);
        //创建sql语句
        StringBuilder findSchool_sql = new StringBuilder("select * from School");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if (conditionList_json_str != null){
            String whereClause = Condition.toWhereClause(conditionList_json_str);
            findSchool_sql.append(whereClause);
        }
        //如果参数不为空，为SQL语句添加LIMIT子句
        if (pagination != null){
            findSchool_sql.append(pagination.toLimitClause(totalNum) + " ");
        }
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt_select_all = JdbcHelper.getConn().prepareStatement(findSchool_sql.toString());
        //执行预编译语句，结果集保存在resultSet中
        ResultSet resultSet = pstmt_select_all.executeQuery();
        //遍历resultSet
        while (resultSet.next()) {
            //根据id查找结果创建school对象
            int id = resultSet.getInt("id");
            String description = resultSet.getString("description");
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");
            School desiredSchool = new School(id,description,no,remarks);
            desiredSchools.add(desiredSchool);
        }
        //关闭对象
        JdbcHelper.close(resultSet,pstmt_select_all,JdbcHelper.getConn());
        return desiredSchools;
    }


    public School find(Integer id,Connection connection) throws SQLException {
        //创建sql语句
        String findSchool_sql = "select * from school where id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(findSchool_sql);
        //为预编译参数赋值
        pstmt.setInt(1,id);
        //根据id查找
        ResultSet resultSet = pstmt.executeQuery();
        resultSet.next();
        School school = new School(
                id,
                resultSet.getString("description"),
                resultSet.getString("no"),
                resultSet.getString("remarks"));
        //关闭连接
        JdbcHelper.close(pstmt,connection);
        //返回school对象
        return school;
    }

    public boolean update(School school,Connection connection) throws SQLException {
        //创建sql语句
        String updateSchool_sql = "UPDATE School set  description=? ,no=? , remarks=? where id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(updateSchool_sql);

        pstmt.setString(1,school.getDescription());
        pstmt.setString(2,school.getNo());
        pstmt.setString(3,school.getRemarks());
        pstmt.setInt(4,school.getId());

        //修改teacher 并判断是否修改成功
        int affectedRowNum = pstmt.executeUpdate();
        JdbcHelper.close(null,pstmt);

        return affectedRowNum>0;

    }

    public boolean add(School school,Connection connection) throws SQLException {
        //创建sql语句
        String addScholl_sql = "INSERT INTO school(description,no,remarks) VALUES" + "(?,?,?)";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(addScholl_sql);

        pstmt.setString(1,school.getDescription());
        pstmt.setString(2,school.getNo());
        pstmt.setString(3,school.getRemarks());

        return pstmt.executeLargeUpdate()>0;
    }

    public boolean delete(Integer id,Connection connection) throws SQLException {
        //创建sql语句
        String deleteSchool_sql = "delete from School where id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteSchool_sql);
        //为预编译参数赋值
        pstmt.setInt(1,id);
        if(pstmt.executeUpdate() == 0){
            JdbcHelper.close(pstmt,JdbcHelper.getConn());
            return false;
        }else {
            JdbcHelper.close(pstmt,JdbcHelper.getConn());
            return  true;
        }
    }

    public boolean delete(School school,Connection connection) throws SQLException {
        //创建sql语句
        String deleteSchool_sql = "DELETE FROM School WHERE id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteSchool_sql);
        //为预编译参数赋值
        pstmt.setInt(1,school.getId());
        if (pstmt.executeLargeUpdate() == 1){
            return true;
        }else {
            return false;
        }
    }
}
